package com.huan.dao.mapper;

import com.huan.pojo.Count;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface CountMapper {
    /**
     * 查询总记录数
     * @return
     */
    @Select("select count(*) from 学生情况")
    int selectTotalCount();

    /**
     * 院系按性别统计人数
     * @return
     */
    @Select("select  `系别`.`name` as name,`学生情况`.`sex` as sex,count(*) as sum from 系别,学生情况 where" +
            " 系别.depatrmentID=学生情况.departmentID\n" +
            "GROUP BY 系别.name, 学生情况.sex")
    List<Count> demo();

    /**
     * 院系按生源地统计人数
     * @return
     */
    @Select("SELECT `系别`.`name` as name, Count(*) as sum, `学生情况`.originPlace as originPlace \n" +
            "FROM `系别`,`学生情况`\n" +
            "WHERE `系别`.depatrmentID=`学生情况`.departmentID\n" +
            "GROUP BY `系别`.`name` , `学生情况`.originPlace;")
    List<Count> countOriginPlace();

    /**
     * 各院系不及格的课程名单
     * @return
     */
    @Select("SELECT 系别.`name` as name, 学生情况.studentID as studentID, 学生情况.`name` as studentName, " +
            "课程档案.`name` as courseName, 选课及成绩.score as score\n" +
            "FROM 系别 INNER JOIN (课程档案 INNER JOIN " +
            "(学生情况 INNER JOIN 选课及成绩 ON 学生情况.studentID = 选课及成绩.studentID) ON 课程档案.courseID = 选课及成绩.courseID) " +
            "ON 系别.depatrmentID = 学生情况.departmentID\n" +
            "WHERE (((选课及成绩.score)<60));")
    List<Count> countFile();

    /**
     * 根据生源地统计学生人数
     * @param begin
     * @param size
     * @return
     */
    @Select("select originPlace,count(*) as sum from 学生情况  group by originPlace")
    List<Count> selectCountPlace(@Param("begin") int begin, @Param("size") int size);

    /**
     * 通过性别统计学生平均成绩
     * @return
     */
    @Select("SELECT 学生情况.sex as sex, 课程档案.`name` as courseName, Avg(选课及成绩.score) as avg\n" +
            "FROM 学生情况 INNER JOIN (课程档案 INNER JOIN 选课及成绩 ON 课程档案.courseID = 选课及成绩.courseID) " +
            "ON 学生情况.studentID = 选课及成绩.studentID\n" +
            "GROUP BY 学生情况.sex, 课程档案.`name`;")
    List<Count> toSexCountAvg();


    /**
     * 各院系按课程的平均成绩查询
     *
     * @return
     */
    @Select("SELECT 系别.`name` as name, 课程档案.`name`as courseName, Avg(选课及成绩.score) AS avg\n" +
            "FROM (系别 INNER JOIN 学生情况 ON 系别.depatrmentID = 学生情况.departmentID) INNER JOIN " +
            "(课程档案 INNER JOIN 选课及成绩 ON 课程档案.courseID = 选课及成绩.courseID) ON 学生情况.studentID = 选课及成绩.studentID\n" +
            "GROUP BY 系别.`name`, 课程档案.`name`")
    List<Count> toCourseAvg();

    /**
     * 学生成绩查询
     * @return
     */
    @Select("SELECT 学生情况.studentID as studentID, 学生情况.`name` as studentName, 选课及成绩.courseID as courseID, 课程档案.`name` as courseName, 选课及成绩.score as score\n" +
            "FROM 学生情况 INNER JOIN (课程档案 INNER JOIN 选课及成绩 ON 课程档案.courseID = 选课及成绩.courseID) ON 学生情况.studentID = 选课及成绩.studentID;\n")
    List<Count> studentCourse();

}
